package DatabasePackage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import DatabasePackage.DatabaseManager.DBTables;

/**
 * This class holds the common actions one needs to interact with various
 * different databases. These actions consists of: 1. Creating the datatables.
 * 2. Logging into the DB. 3. Performing mutating queries such as: inserting and
 * deleting a record. 4. Performing inspection queries.
 * 
 */
public abstract class DBtable {
  String driver;
  String url; 
  String user; 
  String password;
  // scheme is the actual scheme of the concrete DB
  // It's a mapping of the column identifier and its type in such a format
  // that the column name is the key
  protected Map<String, String> scheme = null;
  // keys are a list of the specific scheme keys
  protected List<String> keys = null;
  // sortBy is a list column names specifying the sort order of get requests
  // In case no sort is needed an empty string will be created
  protected List<String> sortBy = null;
  // inDescendingOrder is a boolean flag meant to determine whether the
  // records
  // should be sorted in ascending or descending order. By default, the
  // sorting is in ascending order unless specified in the ctor.
  protected boolean inDescendingOrder = false;
  // We maintain the invariant that the datatables names are identical to the
  // concrete classes "wrapping" them
  protected String dbName = this.getClass().getSimpleName();
  
  // This constructor is meant to force inheritors to initialize the keys and
  // scheme fields
  DBtable(Map<String, String> scheme, List<String> keys, List<String> sortBy) {
    this.scheme = scheme;
    this.keys = keys;
    this.sortBy = sortBy;
    inDescendingOrder = false;
  }
  
  // This constructor is similar to the one above, but it allows initializing
  // the sort order
  DBtable(Map<String, String> scheme, List<String> keys, List<String> sortBy, boolean inDescendingOrder) {
    this.scheme = scheme;
    this.keys = keys;
    this.sortBy = sortBy;
    this.inDescendingOrder = inDescendingOrder;
  }
  
  // /////////////////////////// PARSING methods //////////////////////////////
  /***
   * This method creates the SQL like string of the scheme definition used by
   * the CREATE TABLE clause.
   * 
   * @return A String describing the scheme to be used by CREATE TABLE clause.
   */
  protected String createTableScheme() {
    String sqlScheme = "";
    Set<Entry<String, String>> mapElements = scheme.entrySet();
    // Traversing the elements, and creating the sql like string
    for (Entry<String, String> columnPair : mapElements) {
      String column = columnPair.getKey();
      String type = columnPair.getValue();
      // Concatenating to the return string the pair of ",<column> <value>"
      sqlScheme = sqlScheme + ", " + column + " " + type;
    }
    // Trimming the "," in the begging of sqlScheme
    sqlScheme = sqlScheme.substring(1);
    return sqlScheme;
  }
  
  /**
   * This method constructs a where clause.
   * 
   * @param whereclause
   *          The parameters to the whereClause are specified by the map, in the
   *          format of column name as a key and value as a value
   * @return A sql like string consisting of a where clause in a query
   */
  protected String createWhere(Map<String, byte[]> whereClause) {
    Utility.checkIfNull(whereClause);
    String sqlWhereClause = "";
    // Traversing the elements, and creating the sql like string
    for (Entry<String, byte[]> restriction : whereClause.entrySet()) {
      String column = restriction.getKey();
      // Concatenating to the return string a boolean condition of the form:
      // " AND <column>=<value>
      sqlWhereClause = sqlWhereClause.concat(" AND " + column + "=" + "?");
    }
    // Trimming the " AND" from the start
    sqlWhereClause = sqlWhereClause.substring(5);
    return sqlWhereClause;
  }
  
  
  /**
   * Creates a sort clause to add to a SQL query. The order of the sorting
   * criteria is the same order of columns in sortBy member.
   * 
   * @return A String of a sort clause to add to a SQL query
   */
  protected String createSort() {
    String desc = inDescendingOrder ? " DESC" : "";
    String sortClause = "";
    for (String column : sortBy)
      sortClause = sortClause + ", " + column + desc;
    // Trimming the "," in the begging of columns and values
    sortClause = sortClause.substring(1);
    return sortClause;
  }
  
  // ///////////////////////// PARSING methods END ////////////////////////////
  // //////////////////////////// HELPER methods //////////////////////////////
  /**
   * This function creates a connection with the database specified by
   * createURL.
   * 
   * @return A Connection object for making queries
   * @throws ClassNotFoundException
   *           in case java can't find the appropriate connector to MySQL - in
   *           our case Connector/J
   * @throws SQLException
   *           in case our system is unable to connect to the SQL server
   */
  public Connection connect() throws ClassNotFoundException, SQLException {
    Connection sqlCon = null;
    // Try to connect to the sql server
    Class.forName(driver);
    sqlCon = DriverManager.getConnection(url, user, password);
    
    return sqlCon;
  }
  
  /**
   * This method receives a record, and transforms the record into another type
   * of representation, as specified below.
   * 
   * @param row
   *          A ResultSet that representing a record in the database
   * @return A record, in the format of a List containing a mapping between all
   *         the columns and their corresponding values.
   * @throws SQLException
   *           if a database access error occurs
   */
  protected List<Map<String, byte[]>> createRow(ResultSet row) throws SQLException {
    Utility.checkIfNull(row);
    List<Map<String, byte[]>> res = new LinkedList<Map<String, byte[]>>();
    Map<String, byte[]> tuple = new HashMap<String, byte[]>();
    while (row.next()) {
      // tuple object will hold an entire record from the database table
      tuple = new HashMap<String, byte[]>();
      int numColumns = row.getMetaData().getColumnCount();
      for (int i = 1; i <= numColumns; ++i) {
        String column = row.getMetaData().getColumnName(i);
        byte[] value = row.getString(i)==null?null:row.getString(i).getBytes();
        // Adding a column name and it's value
        tuple.put(column, value);
      }
      res.add(tuple); // Appending to the end of the list
    }
    return res;
  }
  
  // ////////////////////////// HELPER methods END ////////////////////////////
  // /////////////////////////// SERVICE methods //////////////////////////////
  /**
   * This method tries to create a concrete datatable, and if such a table
   * already exists maintains the old one, and does nothing.
   * @param password 
   * @param user 
   * @param url 
   * @param driver 
   * 
   * @throws SQLException
   *           in case our system is unable to connect to the SQL server or in
   *           case a database error has occurred
   * @throws ClassNotFoundException
   *           in case java can't find the appropriate connector to MySQL - in
   *           our case Connector/J
   */
  protected void initializeDB(String driver, String url, String user, String password) throws ClassNotFoundException, SQLException {
    // Trying to establish a connection with the DB
    this.driver = driver;
    this.url = url;
    this.user = user;
    this.password = password;
    Connection con = connect();
    
    try {
      String scheme = createTableScheme();
      String engine = "InnoDB";
      if (this.dbName.equals(CourseDBTable.class.getSimpleName()))
      {
    	  engine = "MyISAM";
      }
      String creation = "CREATE TABLE IF NOT EXISTS " + dbName + " ( " + scheme + " ) ENGINE = "+engine;
    //  throw new ClassNotFoundException(creation);
      Statement stmt = con.createStatement();
      stmt.executeUpdate(creation);
    } finally {
      con.close();
    }
  }
  
  /**
   * This method tries to remove a concrete datatable, and if such a table
   * doesn't exist, it does nothing.
   * 
   * @throws ClassNotFoundException
   *           in case java can't find the appropriate connector to MySQL - in
   *           our case Connector/J
   * @throws SQLException
   *           in case our system is unable to connect to the SQL server or in
   *           case a database error has occurred
   */
  protected void cleanupDB() throws ClassNotFoundException, SQLException {
    // Trying to establish a connection with the DB
    Connection con = connect();
    String removal = "DROP TABLE IF EXISTS " + dbName;
    try {
      Statement stmt = con.createStatement();
      stmt.executeUpdate(removal);
    } finally {
      con.close();
    }
  }
  
  /**
   * This method retrieves a record identifiers (usually the passed item is a
   * key) from a concrete database. In case a record doesn't exist an empty list
   * will be returned.
   * 
   * @param records
   *          A mapping of Strings that correspond to the names of the columns
   *          and the expected values. In case we want to receive all the
   *          elements in the table we will pass an empty mapping.
   * @return records in the format of a List, containing a mapping between all
   *         the columns and their corresponding values.
   * @throws SQLException
   *           in case our system is unable to connect to the SQL server
   * @throws ClassNotFoundException
   *           in case java can't find the appropriate connector to MySQL - in
   *           our case Connector/J
   */
  protected List<Map<String, byte[]>> getItem(Connection con, Map<String, byte[]> records) throws ClassNotFoundException, SQLException {
    Utility.checkIfNull(records);
    // Trying to establish a connection with the DB
    String get = "SELECT * FROM " + dbName;
    if (!records.isEmpty()) {
      // Because of the where clause we specify records
      String whereClause = createWhere(records);
      get = get + " WHERE " + whereClause;
    }
    // Adding an optional sort clause
    
    
    if (!sortBy.isEmpty()) {
      String sortClause = createSort();
      get = get + " ORDER BY " + sortClause;
    }
    
    List<byte[]> insertValues = parseInsertValues(records);
    //String insert = "INSERT INTO " + dbName + " " + insertItems;
    //Statement stmt = con.createStatement();
    java.sql.PreparedStatement pstmt = con.prepareStatement(get);
    for(int i = 0; i< insertValues.size(); i++)
      pstmt.setBytes(i+1, insertValues.get(i));
    
  
    ResultSet res = null;
    List<Map<String, byte[]>> results;
    res = pstmt.executeQuery();
    results = createRow(res);
    return results;
  }
  

  
  /**
   * This methods inserts a record into the database. In case the key recored
   * already exists and the datatable doesn't allow repetition an exception will
   * be returned.
   * 
   * @param record
   *          A record from the database represented by a map of a column and
   *          it's expected value.
   * @throws SQLException
   *           in case our system is unable to connect to the SQL server
   * @throws ClassNotFoundException
   *           in case java can't find the appropriate connector to MySQL - in
   *           our case Connector/J
   * @throws ItemAlreadyInDBException
   *           in case the record already exists in the db
   */
  protected void insertItem(Connection con,Map<String, byte[]> record) throws SQLException, ClassNotFoundException, ItemAlreadyInDBException {
	    Utility.checkIfNull(record);
	    // Trying to establish a connection with the DB
	    // Checking to see if such a record exists already in the DB
	    boolean doesAlreadyExists = false;
      // We obtain a submap from the passes one, that contains only the
      // key mappings
      Map<String, byte[]> recordToSearch = new HashMap<String, byte[]>();
      for (String key : keys)
        if (record.containsKey(key))
          recordToSearch.put(key, record.get(key));
      // Finding out if we have duplicate items, and whether it's OK
      doesAlreadyExists = !getItem(con, recordToSearch).isEmpty();
      if (doesAlreadyExists) {
        String err = "The database table " + dbName + " already has the record" + " specified by the following criteria: "
            + createExplicitWhere(recordToSearch) + " in it.";
        throw new ItemAlreadyInDBException(err);
      }
      String insertItems = parseInsertColumns(record);
      List<byte[]> insertValues = parseInsertValues(record);
      //String insert = "INSERT INTO " + dbName + " " + insertItems;
      //Statement stmt = con.createStatement();
      java.sql.PreparedStatement pstmt = con.prepareStatement("INSERT INTO " + dbName + " " + insertItems);
      for(int i = 0; i< insertValues.size(); i++)
        pstmt.setBytes(i+1, insertValues.get(i));
      pstmt.executeUpdate();
	  }
  
  private String createExplicitWhere(Map<String, byte[]> recordToSearch) {
    Utility.checkIfNull(recordToSearch);
    String sqlWhereClause = "";
    // Traversing the elements, and creating the sql like string
    for (Entry<String, byte[]> restriction : recordToSearch.entrySet()) {
      String column = restriction.getKey();
      String val = new String(restriction.getValue());
      // Concatenating to the return string a boolean condition of the form:
      // " AND <column>=<value>
      sqlWhereClause = sqlWhereClause.concat(" AND " + column + "=" + val);
    }
    // Trimming the " AND" from the start
    sqlWhereClause = sqlWhereClause.substring(5);
    return sqlWhereClause;
  }

  protected List<byte[]> parseInsertValues(Map<String, byte[]> record) {
	    Utility.checkIfNull(record);
	    List<byte[]> values = new LinkedList<byte[]>();
	    for (Entry<String, byte[]> insertPair : record.entrySet()) {
	      // Constructing the list of values
	      values.add(insertPair.getValue());
	    }
	    return values;
	  }

  protected String parseInsertColumns(Map<String, byte[]> record) {
	    Utility.checkIfNull(record);
	    String columns = "";
	    String questionMarks = "";
	    for (Entry<String, byte[]> insertPair : record.entrySet()) {
	      // Constructing the list of columns
	      columns = columns + "," + insertPair.getKey();
	      questionMarks = questionMarks + ",?";
	    }
	    // Trimming the "," in the begging of columns and values
	    columns = columns.substring(1);
	    questionMarks = questionMarks.substring(1);
	    return "(" + columns + ")" + " VALUES("+questionMarks+")";
	  }
  
  protected String parseUpdateColumns(Map<String, byte[]> record) {
	    Utility.checkIfNull(record);
	    String columns = "";
	    for (Entry<String, byte[]> insertPair : record.entrySet()) {
	      // Constructing the list of columns
	      columns = columns + "," + insertPair.getKey() + "=?";
	    }
	    // Trimming the "," in the begging of columns and values
	    columns = columns.substring(1);
	    return columns;
	  }


  
  /**
   * This method removes a record from the database. In case the record does not
   * exist an exception will be thrown.
   * 
   * @param record
   *          A record from the database represented by a map of a column and
   *          it's expected value.
   * @throws ClassNotFoundException
   *           in case java can't find the appropriate connector to MySQL - in
   *           our case Connector/J
   * @throws SQLException
   *           in case our system is unable to connect to the SQL server
   * @throws ItemNotFoundException
   *           in case the record does not exist in the db
   */
  protected void removeItem(Connection con, Map<String, byte[]> record) throws ClassNotFoundException, SQLException, ItemNotFoundException {
    Utility.checkIfNull(record);
    if (record.isEmpty())
      throw new ItemNotFoundException("Cannot find an empty record, you may" + "want to suplly a full record");
    boolean doesAlreadyExists = false;
    doesAlreadyExists = !getItem(con, record).isEmpty();
    String whereClause = createWhere(record);
    if (!doesAlreadyExists) {
      String err = "The database table " + dbName + " doesn't have the " + "record specified by:" + whereClause + " in it";
      throw new ItemNotFoundException(err);
    }
    String delete = "DELETE FROM " + dbName + " WHERE " + whereClause;
    
    java.sql.PreparedStatement pstmt = con.prepareStatement(delete);
    List<byte[]> insertValues = parseInsertValues(record);
    
    for(int i = 0; i< insertValues.size(); i++)
      pstmt.setBytes(i+1, insertValues.get(i));
    
    pstmt.executeUpdate();
  }
  

  // ///////////////////////// SERVICE methods END ////////////////////////////
}
